package web.servlet.userlist.dao;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import web.servlet.userlist.domin.PageBean;
import web.servlet.userlist.domin.User;
import web.servlet.userlist.utils.JDBCUtils;

import java.util.List;

/**
 * @Author: 18148
 * @Date: 2020-06-21 15:56:39
 */
public class UserDaoImpl implements UserDao {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
    String sql = "";

    @Override
    public List<User> selectAll() {
        try {
            sql = "select * from user2";
            List<User> users = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
            return users;
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return null;
        }
    }

    @Override
    public int insert(User user) {
        sql = "insert into user2(name,gender,age,address,qq,email) values(?,?,?,?,?,?)";
        int i = jdbcTemplate.update(sql, user.getName(), user.getGender(), user.getAge(), user.getAddress(), user.getQq(), user.getEmail());
        return i;
    }

    @Override
    public User selectByName(String name) {
        try {
            sql = "select* from user2 where name=?";
            User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), name);
            return user;
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return null;
        }
    }

    @Override
    public int update(int id, User user) {
        sql = "update user2 set name=?,gender=?,age=?,address=?,qq=?,email=? where id=?";
        int i = jdbcTemplate.update(sql, user.getName(), user.getGender(), user.getAge(), user.getAddress(), user.getQq(), user.getEmail(), user.getId());
        return i;
    }

    @Override
    public int delete(int id) {
        sql = "delete from user2 where id=?";
        int i = jdbcTemplate.update(sql, id);
        return i;
    }

    @Override
    public User selectById(int id) {
        try {
            sql = "select* from user2 where id=?";
            User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);
            return user;
        } catch (Exception e) {
            System.out.println(e.getMessage());
            return null;
        }
    }

    @Override
    public User selectByNameAndPassword(User user) {
        sql = "select * from user2 where username=? and password=?";
        try {
            User user1 = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), user.getUsername(), user.getPassword());
            return user1;
        } catch (Exception e) {
            e.getMessage();
            return null;
        }
    }

    @Override
    public int selectTotalCount() {
        sql = "select count(*) from user2";
        try {
            int i = jdbcTemplate.queryForObject(sql,Integer.class);
            return i;
        } catch (Exception e) {
            System.out.println( e.getMessage());
            return 0;

        }

    }

    @Override
    public List<User> selectByPage(int s, int r) {
        sql = "select * from user2 limit ?,?";
        try {
            List<User> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class),s,r);
            return list;
        } catch (Exception e) {
            System.out.println( e.getMessage());
            return null;
        }
    }


}
